Difference between ExecuteNonQuery, ExecuteScalar and  ExecuteReader method

Commands are executed in order to perform any action to the data in a database in SQL server. To execute a command against any database, the Command object must have an open connection with the database and should have a valid Command statement in the CommandText.

SQLCommand includes the following methods for executing commands at an SQL Server database:

ExecuteNonQuery method:

This method is used whenever we have to perform any action on the database which gives results only in the form of rows affected in that particular database.

• It is used to execute SQL queries or stored procedures that perform actions like insertion, deletion, and updating in a database.
• It does not return any actual data from the database. Rather, only the number of rows onto which the modification such as insertion, deletion, and updating has been performed is returned in an integer value result.
• For example: If we want to add the details of a new employee in a table in the database, then the ExecuteNonQuery method will be used.

SqlConnection SqlConnection_object=new SqlConnection();
SqlCommand SqlCommand_object=new SqlCommand();
SqlCommand_object.Connection=SqlConnection_object;
SqlCommand_object.CommandText = “insert into table_name values(‘nikhil’,’27’,’Gurugram’)”;

SqlConnection_object.Open();
Int rows_affected = SqlCommand_object.ExecuteNonQuery();
SqlConnection_object.Close();

Return rows_affected;

ExecuteScalar method:

This method is used whenever a single value result (in the form of single-cell) is to be obtained from the database.

• It is also used to fetch the data of the first column of the first row from the result set from the database.
• A return type is an object in this method. It means a variable should be reserved for holding the return value.

SqlConnection SqlConnection_object=new SqlConnection();
SqlCommand SqlCommand_object=new SqlCommand();
SqlCommand_object.Connection=SqlConnection_object;
SqlCommand_object.CommandText = “select *  from  table_name”;
SqlConnection_object.Open();
Object cell = SqlCommand_object.ExecuteScalar();
SqlConnection_object.Close();
Return cell;

Explanation: This method will return only the first column of the first row in the table table_name.

ExecuteReader method:

ExecuteReader method is used to execute SQL queries or stored procedures that return a set of rows from the database.

• It is generally used with SELECT command where the query is required to fetch a set of data from the database.
• This method allows a way of reading a forward-only set of rows from the database.
• Its return type is DataReader and a DataReader object must always be declared in order to hold the result returned by the query.

SqlConnection SqlConnection_object=new SqlConnection();
SqlCommand SqlCommand_object=new SqlCommand();
SqlCommand_object.Connection=SqlConnection_object;
SqlCommand_object.CommandText = “select *  from  table_name”;

SqlConnection_object.Open();
SqlDataReader dr = SqlCommand_object.ExecuteReader();
SqlConnection_object.Close();

Explanation: This method will help in fetching all the data present in the table table_name and it will be contained into the SqlDataReader object dr.